package com.ustcinfo.study.hive.r4.wangzhigang;

import java.sql.*;

/**
 * @describe: 连接到hive，执行hivesql
 * @author: wang.zhigang
 * @date: 2018/8/3 19:26
 */
public class ConnectToHive {

    //连接地址、用户名、密码
    private static String url="jdbc:hive2://node3:10000/default";
    private static String userName="root";
    private static String password="";

    //hive驱动名
    private static String driver="org.apache.hive.jdbc.HiveDriver";

    private static Connection conn=null;
    private static Statement statement=null;
    private static ResultSet res=null;

    public static void main(String[] args){

        //加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            //连接到hive服务
            conn=DriverManager.getConnection(url,userName,password);
            statement=conn.createStatement();

            //新建dns_wangzhigang表 字段：ip, domain_name, access_time, target_ip, status
            String createTable="create table dns_wangzhigang(ip string, domain_name string, access_time string, target_ip string, status string)row format delimited fields terminated by '#' ";
            if(statement.execute(createTable)){
                System.out.println("建表成功");
            }

            //从本地导入数据
            String putData="load data local inpath '/home/dnsSample' into table dns_wangzhigang";
            if(statement.execute(putData)){
                System.out.println("导入成功");
            }

            //查询 统计同一域名访问的次数 查询访问最多的前100条数据
            String countDns="select domain_name,count(*) as num from dns_wangzhigang group by domain_name order by num desc limit 100";
            res=statement.executeQuery(countDns);
            while(res.next()){
                System.out.print("domain_name:"+res.getString(1)+" ");
                System.out.println("count:"+res.getInt(2));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(res!=null){
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }


    }


}
